CREATE  FUNCTION fn_asi_SoftCreditCMDM_Exists(
        @pTransNumber int,
        @pOrignatingActivitySeqn int,
        @pSoftCreditId as varchar(50))
  RETURNS bit
  AS
  BEGIN
      /*  JB 26-May-06
      Returns a flag to indicate if there are CM/DM applied to an original Soft Credit value.
      The original soft credit is identified in Trans_SoftCredit by
      the combination of TRANS_NUMBER and ORIGINATING_ACTIVITY_SEQN.
      Subsequent CM/DM will have the same ORIGINATING_ACTIVITY_SEQN
      but different TRANS_NUMBER
      */
      declare @exists bit
      set @exists = 0
      if exists (
            select 1 from Trans_SoftCredit
            where
                 ORIGINATING_ACTIVITY_SEQN = @pOrignatingActivitySeqn
                 and TRANS_NUMBER <> @pTransNumber
                 and SOFT_CREDIT_ID=@pSoftCreditId
      )
        set @exists = 1

      return @exists
  END

GO
GRANT EXECUTE ON  [dbo].[fn_asi_SoftCreditCMDM_Exists] TO [IMIS]
GO